// 初始化数据库
const fs = require('fs');
const path = require('path');

function initDB() {
  const dbPath = path.join(__dirname, 'students.db');
  
  if (!fs.existsSync(dbPath)) {
    fs.writeFileSync(dbPath, '');
  }
  
  const db = new SQL.Database(dbPath);
  
  // 创建班级表
  db.run(`CREATE TABLE IF NOT EXISTS classes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
  )`);

  // 创建学生表
  db.run(`CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    class_id INTEGER,
    FOREIGN KEY(class_id) REFERENCES classes(id)
  )`);

  return db;
}

let db;

try {
  db = initDB();
} catch (err) {
  console.error('数据库连接失败:', err);
  process.exit(1);
}

// 显示对应功能界面
function showImport() {
  document.getElementById('import-section').classList.remove('hidden');
  document.getElementById('query-section').classList.add('hidden');
}

function showQuery() {
  document.getElementById('query-section').classList.remove('hidden');
  document.getElementById('import-section').classList.add('hidden');
}

// 文件上传处理
document.getElementById('file-input').addEventListener('change', function(e) {
  const file = e.target.files[0];
  const reader = new FileReader();

  reader.onload = function(event) {
    const data = event.target.result;
    let students = [];

    if (file.name.endsWith('.txt')) {
      const className = file.name.replace('.txt', '');
      students = data.split('\n').map(name => ({ name: name.trim(), className }));
    } else if (file.name.endsWith('.xlsx')) {
      const workbook = XLSX.read(data, { type: 'array' });
      const sheet = workbook.Sheets[workbook.SheetNames[0]];
      students = XLSX.utils.sheet_to_json(sheet);
    }

    // 显示预览
    const preview = document.getElementById('file-preview');
    preview.innerHTML = `
      <h4>发现 ${students.length} 条记录</h4>
      <table>
        <tr><th>班级</th><th>姓名</th></tr>
        ${students.map(s => `<tr><td>${s.className}</td><td>${s.name}</td></tr>`).join('')}
      </table>
    `;
  };

  reader.readAsArrayBuffer(file);
});

// 上传到数据库
function uploadFile() {
  const students = Array.from(document.querySelectorAll('#file-preview tr')).slice(1)
    .map(tr => ({
      className: tr.cells[0].textContent,
      name: tr.cells[1].textContent
    }));

  // 事务处理
  db.run('BEGIN TRANSACTION');
  try {
    console.log('开始导入', students);
    students.forEach(student => {
      // 插入或获取班级ID
      let classId = db.exec(
        `SELECT id FROM classes WHERE name = ?`, [student.className]
      );
// 原代码结尾缺少分号，添加分号以符合 JavaScript 语法规范
      if (!classId || classId.length === 0); {
        db.run(`INSERT INTO classes (name) VALUES (?)`, [student.className], function(err) {
          if (err) throw err;
        });
        classId = db.exec('SELECT last_insert_rowid()').values[0][0];
      // 原代码中此处存在逻辑错误，if 语句条件后多了分号，导致后续代码块不会根据条件执行
      // 移除多余的分号，使代码逻辑正确
      if (!classId || classId.length === 0) {
        db.run(`INSERT INTO classes (name) VALUES (?)`, [student.className], function(err) {
          if (err) throw err;
        });
        classId = db.exec('SELECT last_insert_rowid()')[0].values[0][0];
      } else {
        classId = classId.values[0][0];
      }

      // 插入学生
      db.run(
        `INSERT INTO students (name, class_id) VALUES (?, ?)`,
        [student.name, classId],
        function(err) {
          if (err) throw err;
        }
      );
    });

    db.run('COMMIT');
    console.log('事务提交成功');
    
    const toast = document.createElement('div');
    toast.className = 'success-toast';
    toast.textContent = `成功导入 ${students.length} 条数据！`;
    document.body.appendChild(toast);
    setTimeout(() => toast.remove(), 3000);
  } catch (e) {
    db.run('ROLLBACK');
    console.error('事务回滚:', e);
    
    const toast = document.createElement('div');
    toast.className = 'error-toast';
    toast.textContent = '导入失败：' + e.message;
    document.body.appendChild(toast);
    setTimeout(() => toast.remove(), 3000);
  }
}

// 学生查询功能
function searchStudents() {
  const keyword = document.getElementById('search-input').value.toLowerCase();
  const query = `
    SELECT c.name as className, s.name as studentName 
    FROM students s
    JOIN classes c ON s.class_id = c.id
    WHERE s.name LIKE '%${keyword}%' OR c.name LIKE '%${keyword}%'
  `;

  const results = db.exec(query);
  const container = document.getElementById('results');
  
  if (results.length > 0) {
    const data = results[0].values;
    container.innerHTML = `
      <h4>找到 ${data.length} 条结果</h4>
      <table>
        <tr><th>班级</th><th>姓名</th></tr>
        ${data.map(row => `<tr><td>${row[0]}</td><td>${row[1]}</td></tr>`).join('')}
      </table>
    `;
  } else {
    container.innerHTML = '<p>未找到匹配结果</p>';
  }
}